package test.晶晶;

import cn.hutool.core.io.FileUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.DSFactory;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import lombok.SneakyThrows;
import org.apache.commons.lang3.StringUtils;

import java.util.*;

/**
 * @Author: gcr
 * @Date: 2022-6-9 9:28
 */
public class 晶晶数据获取 {
    @SneakyThrows
    public static void main(String[] args) {
        // 2022年6月9日09:28:21 根据pmid 获取 期刊和期刊对应的分区

        Db use = Db.use(DSFactory.get("server"));
        Db useLocal = Db.use();
        String fileName = "晶晶统计+++" + System.currentTimeMillis();

        List<String> strings = FileUtil.readLines("F:\\qfworkspace\\idea\\update-pm-data\\src\\main\\java\\test\\晶晶\\je", "UTF-8");

        String join = String.join(",", strings);
        Set<String> find = new HashSet<>();
        String sql = "select pmid , med_nlm_id from pubmed where pmid in (" + join + " )";
        Map<String, String> pmidAndJour = new HashMap<>();
        // 期刊id对应zky表  id
        Map<String, String> jouAndZkyId = new HashMap<>();
        Map<String, String> jouidName = new HashMap<>();
        Map<String, Entity> zkyidAndENtity = new HashMap<>();
        // 期刊对应中科院实体
        Map<String, Entity> jourIDAndzkyEntity = new HashMap<>();
        List<Entity> query = use.query(sql);
        Set<String> nlmid = new HashSet<>();
        for (Entity entity : query) {
            String med_nlm_id = entity.getStr("med_nlm_id");
            String pmid = entity.getStr("pmid");
            find.add(pmid);
            if (StringUtils.isNotEmpty(med_nlm_id)) {
                nlmid.add(med_nlm_id);
                pmidAndJour.put(pmid, med_nlm_id);
            }
        }

        List<Entity> jourList = new ArrayList<>();
        for (String s : nlmid) {
            String sqlJour = "select nc_nlm_id, zky,jour from jour_merge_supp_new where nc_nlm_id =  ( '" + s + "'  )";
            List<Entity> query1 = useLocal.query(sqlJour);
            if (query1.size() > 0) {
                jourList.add(query1.get(0));
            }
        }
        String zkySQL = "select  *  from zky_final_zfl ";

        List<Entity> zkyLS = useLocal.query(zkySQL);
        for (Entity entity : jourList) {
            String nc_nlm_id = entity.getStr("nc_nlm_id");
            String jour = entity.getStr("jour");
            String zkyid = entity.getStr("zky");
            if (StringUtils.isNotEmpty(nc_nlm_id) && StringUtils.isNotEmpty(zkyid)) {
                jouAndZkyId.put(nc_nlm_id, zkyid);
                jouidName.put(nc_nlm_id, jour);
            }

        }
        for (Entity zkyL : zkyLS) {
            String id = zkyL.getStr("id");
            zkyidAndENtity.put(id, zkyL);
        }
        // 组合期刊对应的中科院entity
        jouAndZkyId.forEach((k, v) -> {
            boolean b = zkyidAndENtity.containsKey(v);
            if (b) {
                Entity entity = zkyidAndENtity.get(v);
                jourIDAndzkyEntity.put(k, entity);
            }
        });

        List<AcaStaticDetail> resE = new ArrayList<>();
        pmidAndJour.forEach((k, v) -> {
            if (jourIDAndzkyEntity.containsKey(v)) {
                Entity entity = jourIDAndzkyEntity.get(v);
                String qu = entity.getStr("large_one_subject") + " " + entity.getStr("large_one_partition") + "区";
                Map<String, String> jouPart = new HashMap<>();
                jouPart.put(v, qu);
                AcaStaticDetail acaStaticDetail = new AcaStaticDetail();
                acaStaticDetail.setPmid(k);
                acaStaticDetail.setNlmid(v);
                acaStaticDetail.setNlmidName(jouidName.get(v));
                acaStaticDetail.setFq(qu);
                resE.add(acaStaticDetail);
            } else {
                AcaStaticDetail acaStaticDetail = new AcaStaticDetail();
                acaStaticDetail.setPmid(k);
                acaStaticDetail.setNlmid(v);
                acaStaticDetail.setNlmidName(jouidName.get(v));
                acaStaticDetail.setFq("");
                resE.add(acaStaticDetail);
            }
        });

        boolean remove = strings.removeAll(find);
        for (String string : strings) {
            AcaStaticDetail acaStaticDetail = new AcaStaticDetail();
            acaStaticDetail.setPmid(string);
            acaStaticDetail.setNlmid("");
            acaStaticDetail.setNlmidName("");
            acaStaticDetail.setFq("");
            resE.add(acaStaticDetail);
        }


        String fileNamePath = "/usr/local/" + fileName + ".xls";
        ExcelWriter writer = ExcelUtil.getWriter(fileNamePath);
        writer.merge(5, fileName + "统计信息");
        writer.setColumnWidth(-1, 25);
        writer.write(resE, true);
        writer.close();

        System.out.println("jieshu");


    }
}
